/**************************************************************************
	Replication do-file: "Corruption in Customs"
	Cyril Chalendard, Ana Fernandes, Gael Raballand and Bob Rijkers
	
	Created on: 01/07/2022
**************************************************************************/

clear all
set more off, perm
cap log close
cls

* ----------------------- DIRECTORIES AND FOLDERS ----------------------- *

* Directories in which data are stored
global main "PUT YOUR DIRECTORY PATH HERE"
cd "$folder"

* Folders
global outputdata = "$main\Output Data"
cap mkdir "$main\Tables"
global tables = "$main\Tables"

* ----------------------------- BEGINS HERE ----------------------------- *

* -------
* Dataset
* -------
use "$outputdata\CFRR.dta", clear // open dataset

* Sample
keep if sample_int==1 & eis_f!=. // define sample

* Collapse
collapse (mean) wfr eis_f eis_nowfr_f risk red_i sig_c_f99 , by(nc_sem key_dec insp_f) // collapse at the semester-inspector-broker

* New variables
egen couple = group(key_dec insp_f)
tsset couple nc_sem
gen lageis_f = l.eis_f
gen lagwfr = l.wfr
gen lagsig_c_f99 = l.sig_c_f99
replace lagwfr=. if nc_sem!=12
replace wfr=. if nc_sem<11


* --------
* Table A5
* --------

* Matrix
mat tableA5_1 = J(14,7,.)
mat tableA5_2 = J(21,7,.)

* Coefficients
** Without space for significance
qui: pwcorr eis_f lageis_f sig_c_f99 lagsig_c_f99 wfr lagwfr eis_nowfr_f , sig obs star(0.01) // estimate correlations and show significance and observations
mat coef = r(C) // store results in a matrix
local b = 1
local s = 1
forvalues a = 1(1)7 {
	
	local i = `a'
	local j = `a'

	forvalues k = `s'(2)13 {
		mat tableA5_1[`k',`b'] = coef[`i',`j']
		local i = `i'+1
	}
	
	local j = `j'+1
	local b = `b'+1
	local s = `s'+2
}
** With space for significance
local b = 1
local s = 1
forvalues a = 1(1)7 {
	
	local i = `a'
	local j = `a'

	forvalues k = `s'(3)21 {
		mat tableA5_2[`k',`b'] = coef[`i',`j']
		local i = `i'+1
	}
	
	local j = `j'+1
	local b = `b'+1
	local s = `s'+3
}

* Significance
qui: pwcorr eis_f lageis_f sig_c_f99 lagsig_c_f99 wfr lagwfr eis_nowfr_f , sig obs star(0.01) // estimate correlations and show significance and observations
mat sig = r(sig) // store significance in a matrix
local b = 1
local s = 2
forvalues a = 1(1)7 {
	
	local i = `a'
	local j = `a'

	forvalues k = `s'(3)21 {
		mat tableA5_2[`k',`b'] = sig[`i',`j']
		local i = `i'+1
	}
	
	local j = `j'+1
	local b = `b'+1
	local s = `s'+3
}

* Observations
** One-way correlations
*** Without space for significance
local var `" "eis_f" "lageis_f" "sig_c_f99" "lagsig_c_f99" "wfr" "lagwfr" "eis_nowfr_f" "'
local s = 2
local b = 1
forvalues i = 1(1)7 {
	
	local x: word `i' of `var'
	
	qui: corr `x' // estimate one-way correlation
	mat tableA5_1[`s',`b'] = `r(N)'
	local s = `s'+2
	local b = `b'+1
}
** With space for significance
local var `" "eis_f" "lageis_f" "sig_c_f99" "lagsig_c_f99" "wfr" "lagwfr" "eis_nowfr_f" "'
local s = 3
local b = 1
forvalues i = 1(1)7 {
	
	local x: word `i' of `var'
	
	qui: corr `x' // estimate one-way correlation
	mat tableA5_2[`s',`b'] = `r(N)'
	local s = `s'+3
	local b = `b'+1
}
** Two-way correlations
*** Without space for significance
local var `" "lageis_f" "sig_c_f99" "lagsig_c_f99" "wfr" "lagwfr" "eis_nowfr_f" "'
local s = 4
forvalues i = 1(1)6 {
	
	local x: word `i' of `var'
	
	qui: pwcorr eis_f `x', obs // estimate two-way correlation and capture observations
	mat tableA5_1[`s',1] = `r(N)'
	local s = `s'+2
}
local var `" "sig_c_f99" "lagsig_c_f99" "wfr" "lagwfr" "eis_nowfr_f" "'
local s = 6
forvalues i = 1(1)5 {
	
	local x: word `i' of `var'
	
	qui: pwcorr lageis_f `x', obs // estimate two-way correlation and capture observations
	mat tableA5_1[`s',2] = `r(N)'
	local s = `s'+2
}
local var `" "lagsig_c_f99" "wfr" "lagwfr" "eis_nowfr_f" "'
local s = 8
forvalues i = 1(1)4 {
	
	local x: word `i' of `var'
	
	qui: pwcorr sig_c_f99 `x', obs // estimate two-way correlation and capture observations
	mat tableA5_1[`s',3] = `r(N)'
	local s = `s'+2
}
local var `" "wfr" "lagwfr" "eis_nowfr_f" "'
local s = 10
forvalues i = 1(1)3 {
	
	local x: word `i' of `var'
	
	qui: pwcorr lagsig_c_f99 `x', obs // estimate two-way correlation and capture observations
	mat tableA5_1[`s',4] = `r(N)'
	local s = `s'+2
}
local var `" "lagwfr" "eis_nowfr_f" "'
local s = 12
forvalues i = 1(1)2 {
	
	local x: word `i' of `var'
	
	qui: pwcorr wfr `x', obs // estimate two-way correlation and capture observations
	mat tableA5_1[`s',5] = `r(N)'
	local s = `s'+2
}
qui: pwcorr lagwfr eis_nowfr_f, obs // estimate two-way correlation and capture observations
mat tableA5_1[14,6] = `r(N)'
*** With space for significance
local var `" "lageis_f" "sig_c_f99" "lagsig_c_f99" "wfr" "lagwfr" "eis_nowfr_f" "'
local s = 6
forvalues i = 1(1)6 {
	
	local x: word `i' of `var'
	
	qui: pwcorr eis_f `x', obs // estimate two-way correlation and capture observations
	mat tableA5_2[`s',1] = `r(N)'
	local s = `s'+3
}
local var `" "sig_c_f99" "lagsig_c_f99" "wfr" "lagwfr" "eis_nowfr_f" "'
local s = 9
forvalues i = 1(1)5 {
	
	local x: word `i' of `var'
	
	qui: pwcorr lageis_f `x', obs // estimate two-way correlation and capture observations
	mat tableA5_2[`s',2] = `r(N)'
	local s = `s'+3
}
local var `" "lagsig_c_f99" "wfr" "lagwfr" "eis_nowfr_f" "'
local s = 12
forvalues i = 1(1)4 {
	
	local x: word `i' of `var'
	
	qui: pwcorr sig_c_f99 `x', obs // estimate two-way correlation and capture observations
	mat tableA5_2[`s',3] = `r(N)'
	local s = `s'+3
}
local var `" "wfr" "lagwfr" "eis_nowfr_f" "'
local s = 15
forvalues i = 1(1)3 {
	
	local x: word `i' of `var'
	
	qui: pwcorr lagsig_c_f99 `x', obs // estimate two-way correlation and capture observations
	mat tableA5_2[`s',4] = `r(N)'
	local s = `s'+3
}
local var `" "lagwfr" "eis_nowfr_f" "'
local s = 18
forvalues i = 1(1)2 {
	
	local x: word `i' of `var'
	
	qui: pwcorr wfr `x', obs // estimate two-way correlation and capture observations
	mat tableA5_2[`s',5] = `r(N)'
	local s = `s'+3
}
qui: pwcorr lagwfr eis_nowfr_f, obs // estimate two-way correlation and capture observations
mat tableA5_2[21,6] = `r(N)'


* Export: table without significance
* ----------------------------------
putexcel set "$tables\Table A5.xlsx", replace sheet(TableA5_1) // create a new excel spreadsheet

* Main Title
putexcel (A1:I1), merge hcenter vcenter
putexcel A1 = "Correlation matrix"

* Names of variables
local cells `" "C" "D" "E" "F" "G" "H" "I" "'
local labels `" "Excess interaction share" "Lagged excess interaction share" "Excess interaction indicator" "Lagged excess interaction indicator" "Withheld from randomization (WFR)" "Lagged WFR" "Random excess interaction share" "'
local f = 3
forvalues i = 1(1)7 {

	local x: word `i' of `labels'
	local y: word `i' of `cells'
	
	putexcel A`f' = "`x'", left vcenter
	putexcel `y'2 = "`x'", hcenter vcenter
	local f = `f'+2
}

* Names of statistics
forvalues i = 3(2)15 {
	putexcel B`i' = "\rho", left vcenter
}
forvalues i = 4(2)16 {
	putexcel B`i' = "N", left vcenter
}

* Coefficients
putexcel C3 = matrix(tableA5_1), nformat(0.000) hcenter vcenter


* Export: table with significance
* -------------------------------
putexcel set "$tables\Table A5.xlsx", modify sheet(TableA5_2) // modify existing excel spreadsheet

* Main Title
putexcel (A1:I1), merge hcenter vcenter
putexcel A1 = "Correlation matrix"

* Names of variables
local cells `" "C" "D" "E" "F" "G" "H" "I" "'
local labels `" "Excess interaction share" "Lagged excess interaction share" "Excess interaction indicator" "Lagged excess interaction indicator" "Withheld from randomization (WFR)" "Lagged WFR" "Random excess interaction share" "'
local f = 3
forvalues i = 1(1)7 {

	local x: word `i' of `labels'
	local y: word `i' of `cells'
	
	putexcel A`f' = "`x'", left vcenter
	putexcel `y'2 = "`x'", hcenter vcenter
	local f = `f'+3
}

* Names of statistics
forvalues i = 3(3)23 {
	putexcel B`i' = "\rho", left vcenter
}
forvalues i = 4(3)23 {
	putexcel B`i' = "sig. level", left vcenter
}
forvalues i = 5(3)23 {
	putexcel B`i' = "N", left vcenter
}

* Coefficients
putexcel C3 = matrix(tableA5_2), nformat(0.000) hcenter vcenter


* -------------------------------- ENDS HERE -------------------------------- *